
*	This code generates results related to Mutual fund, i.e., Table 7;

options threads cpucount=8 sortsize=32G bufno=8;
dm log 'clear';

%let mainfolder=D:\Dropbox\Characteristics Factors\RFS_Replication\;

libname mylib "&mainfolder.\Data_Cleaned";

proc datasets library=work nolist kill;
run;
quit;

data ff3_factors;
set mylib.allfactors(keep=date rf mktrf smb hml);
date=intnx('month',date,0,'e');
format date yymmddn8.;
run;

proc sort data=ff3_factors;
by date;
run;
* CPI data downloaded from Fred: https://fred.stlouisfed.org/series/CPIAUCSL;
PROC IMPORT OUT= cpi 
            DATAFILE= "&mainfolder.\Data_Cleaned\CPIAUCSL.csv" 
            DBMS=CSV REPLACE;
     GETNAMES=YES;
     DATAROW=2; 
RUN;
* move forward to 1 month to return timing, since this size filter applies to tna from previous month ;
data cpi;
set cpi;
date=intnx('month',date,1,'e');
format date yymmddn8.;
rename CPIAUCSL=cpi;
year=year(date);
run;

data returns;
set mylib.mf_monthly(keep=wficn date tna rret weight);
where not missing(wficn);
date=intnx('month',date,0,'e');
format date yymmddn8.;
run;

proc sort data=returns nodupkey;
by wficn date;
run;

proc sort data=cpi;
by year;
run;

proc means data=cpi noprint;
by year;
var cpi;
output out=cpi1(drop=_freq_ _type_) mean=;
run;

proc sql;
create table cpi2 
as select distinct a.year,a.cpi,15*a.cpi/b.cpi as mintna15
from cpi1 as a,cpi1(where=(year=2011)) as b
order by a.year;
quit;

proc sql;
create table cpi 
as select distinct a.year,a.mintna15,5*a.cpi/b.cpi as mintna5
from cpi2 as a,cpi1(where=(year=2006)) as b
order by a.year;
quit;

proc sort data=cpi nodupkey;
by year;
run;

data returns;
set returns;
year=year(date);
run;

proc sort data=returns;
by year;
run;

data returns(drop=year);
merge returns(in=in1) cpi;
by year;
if in1;
run;

proc sort data=returns out=ret(rename=(wficn=permno)) nodupkey;
by date wficn;
run;

data ret(drop=rf rret);
merge ret(keep=permno date rret tna mintna5 mintna15 weight in=in1) ff3_factors(keep=date rf in=in2);
by date;
if in1 and in2;
excessret=rret-rf;
run;

data lagme(keep=permno date lagme);
set ret;
date=intnx('month',date,1,'e');
format date yymmddn8.;
rename tna=lagme;
run;

proc sort data=lagme nodupkey;
by permno date;
where not missing(permno) and not missing(date) and not missing(lagme);
run;

proc sort data=ret;
by permno date;
run;

data all;
merge lagme(in=in1) ret(keep=permno date excessret weight mintna5 mintna15 in=in2);
by permno date;
if in2;
run;

%let enddate='31DEC2023'd;

proc datasets library=work nolist;
save all ff3_factors;
run;

*macro to reorder factors so that the output csv is the same order as reported table;
%macro reorder(indata=);

data &indata;
set &indata;
if factor in ('smb',"smb_ff5") then id=1;
if factor in ('hml') then id=2;
if factor in ('rmw') then id=3;
if factor in ('cma') then id=4;
if factor in ('ia') then id=5;
if factor in ('roe') then id=6;
if factor in ('pmu') then id=7;
if factor in ('qmj') then id=8;
if factor in ('umd') then id=9;
if factor in ('mgmt') then id=10;
if factor in ('perf') then id=11;
if factor in ('fin') then id=12;
if factor in ('pead') then id=13;
if factor in ('avg') then id=14;
if factor in ('avg_nosmb') then id=15;
if factor in ('avg_nobf') then id=16;
if factor in ('avg_nobfq') then id=17;
if factor in ('avg_nosmbbf') then id=18;
if factor in ('avg_nosmbbfq') then id=19;
run;

proc sort data=&indata out=&indata(drop=id);
by id;
run;

%mend reorder;

data macro(drop=date rename=(date1=date));
set mylib.macro_monthly(keep=date bw);
where not missing(bw);
date1=intnx('month',date,1,'e');
format date1 yymmddn8.;
run;

proc sort data=macro;
by date;
where date<=&enddate;
run;

%macro betaport(factor=);

dm 'odsresults; clear';

proc datasets library=work nolist;
save macro all ff3_factors bw all1 beta table1 avgspread alltable1;
run;

%if &factor=umd or &factor=perf %then %do;

proc sort data=all1 out=port1(keep=date permno lagme beta1_&factor excessret) nodupkey;
by date permno;
where not missing(beta1_&factor) and '01JAN1980'd<=date<=&enddate and freq_&factor>=&minday and &filter;
run;

%end;

%else %if &factor=pead %then %do;

proc sort data=all1 out=port1(keep=date permno lagme beta1_&factor excessret) nodupkey;
by date permno;
where not missing(beta1_&factor) and '01JAN1980'd<=date<=&enddate and freq_&factor>=&minday and &filter;
run;

%end;

%else %do;

proc sort data=all1 out=port1(keep=date permno lagme beta1_&factor excessret) nodupkey;
by date permno;
where not missing(beta1_&factor) and '01JAN1980'd<=date<=&enddate and freq_&factor>=&minmonth and &filter;
run;

%end;

proc univariate data=port1(where=(not missing(beta1_&factor))) noprint;
by date;
var beta1_&factor;
output out=nyse pctlpre=P_ pctlpts= 10 to 90 by 10;
run;

proc sort data=nyse;
by date;
run;

data port1(drop=P_10 P_20 P_30 P_40 P_50 P_60 P_70 P_80 P_90);
merge port1 nyse;
by date;
if not(beta1_&factor=.) and beta1_&factor<P_10 then cgogroup=0;
if P_10<=beta1_&factor<P_20 then cgogroup=1;
if P_20<=beta1_&factor<P_30 then cgogroup=2;
if P_30<=beta1_&factor<P_40 then cgogroup=3;
if P_40<=beta1_&factor<P_50 then cgogroup=4;
if P_50<=beta1_&factor<P_60 then cgogroup=5;
if P_60<=beta1_&factor<P_70 then cgogroup=6;
if P_70<=beta1_&factor<P_80 then cgogroup=7;
if P_80<=beta1_&factor<P_90 then cgogroup=8;
if beta1_&factor>=P_90 then cgogroup=9;
run;

data port1;
set port1;
portfolio=cgogroup;
if portfolio=. or date=. then delete;
drop cgogroup;
run;

proc sort data=port1;
by date portfolio;
run;

proc means data=port1 noprint;
var excessret;
by date portfolio;
output out=group_ret(drop=_type_ _Freq_) mean=excessret;
run;

proc sql;
create table group_ret
as select date,portfolio,excessret
from group_ret 
group by date
having count(portfolio)=10
order by date,portfolio;
quit;

proc sql;
create table group_ret_6
as select distinct a.date,(a.excessret-b.excessret) as excessret,66 as portfolio
from group_ret as a,group_ret as b
where a.date=b.date and a.portfolio=9 and b.portfolio=0;
quit;

data spread;
set group_ret group_ret_6;
run; 

proc sort data=spread;
by date;
where portfolio=0 or portfolio=9 or portfolio=66;
run;

data spread;
merge spread(in=in1) ff3_factors(keep=date mktrf smb hml in=in2) bw(in=in3);
by date;
if in1 and in2 and in3;
run;

data tmp;
set spread;
proxy="&factor";
keep date excessret portfolio proxy;
run;

proc append data=tmp base=avgspread force;
run;
* conditional return spread ;
proc sort data=spread nodupkey;
by portfolio high date;
where not missing(excessret) and not missing(high);
run;

proc sort data=spread out=date(keep=date) nodupkey;
by date;
run;

data firstdate;
set date;
if _n_=1;
run;

proc sort data=date;
by descending date;
run;

data lastdate;
set date;
if _n_=1;
run;

ods output parameterestimates=excessret;
ods listing close;
proc model data=spread;
 by portfolio high;
 endo excessret;
 instruments / intonly;
 parms b0;
 excessret=b0;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=excessret(keep=portfolio high parameter estimate tvalue) out=excessret(keep=portfolio high estimate tvalue);
by portfolio;
where parameter='b0';
run;

ods output parameterestimates=capmalpha;
ods listing close;
proc model data=spread;
 by portfolio high;
 endo excessret;
 exog mktrf;
 instruments _exog_;
 parms b0 b1;
 excessret=b0+b1*mktrf;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=capmalpha(keep=portfolio high parameter estimate tvalue) out=capmalpha(keep=portfolio high estimate tvalue);
by portfolio;
where parameter='b0';
run;

data spread;
set spread;
mktrf1=mktrf*high;
run;

proc sort data=spread nodupkey;
by portfolio date;
where not missing(excessret);
run;

ods output parameterestimates=excessret1;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog high;
 instruments _exog_;
 parms b0 b2;
 excessret=b0+b2*high;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=excessret1(keep=portfolio parameter estimate tvalue) out=excessret1(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b2';
run;

ods output parameterestimates=capmalpha1;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog mktrf high mktrf1;
 instruments _exog_;
 parms b0 b1 b2 b3;
 excessret=b0+b1*mktrf+b2*high+b3*mktrf1;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=capmalpha1(keep=portfolio parameter estimate tvalue) out=capmalpha1(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b2';
run;

data f(drop=high);
merge excessret(where=(high=1) rename=(estimate=exret_high tvalue=tret_high)) capmalpha(where=(high=1) rename=(estimate=alpha_high tvalue=talpha_high)) 
 excessret(where=(high=0) rename=(estimate=exret_low tvalue=tret_low)) capmalpha(where=(high=0) rename=(estimate=alpha_low tvalue=talpha_low)) 
 excessret1(rename=(estimate=exret_diff tvalue=tret_diff)) capmalpha1(rename=(estimate=alpha_diff tvalue=talpha_diff));
by portfolio;
exret_high=exret_high*100;
alpha_high=alpha_high*100;
exret_low=exret_low*100;
alpha_low=alpha_low*100;
exret_diff=exret_diff*100;
alpha_diff=alpha_diff*100;
run;
*************************************;
* predictive reg ;
proc sort data=spread nodupkey;
by portfolio date;
where not missing(excessret);
run;

ods output parameterestimates=excessretbeta;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog bw;
 instruments _exog_;
 parms b0 b1;
 excessret=b0+b1*bw;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=excessretbeta(keep=portfolio parameter estimate tvalue) out=excessretbeta(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b1';
run;

ods output parameterestimates=ff3beta;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog bw mktrf smb hml;
 instruments _exog_;
 parms b0 b1 b2 b3 b4;
 excessret=b0+b1*bw+b2*mktrf+b3*smb+b4*hml;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=ff3beta(keep=portfolio parameter estimate tvalue) out=ff3beta(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b1';
run;

data f1;
merge excessretbeta(rename=(estimate=exret_beta tvalue=tret_beta)) ff3beta(rename=(estimate=ff3_beta tvalue=tff3_beta));
by portfolio;
factor="&factor";
exret_beta=exret_beta*100;
ff3_beta=ff3_beta*100;
run;

data f;
merge f f1;
by portfolio;
run;
* add date ;
proc sql;
create table f
as select distinct a.*,b.date as firstdate format=yymmddn8.
from f as a,firstdate as b;
quit;

proc sql;
create table f
as select distinct a.*,b.date as lastdate format=yymmddn8.
from f as a,lastdate as b;
quit;

proc sort data=f;
by portfolio;
run;

proc append data=f base=table1 force;
run;

%mend betaport;

%macro suball(avgexclude=);

dm 'odsresults; clear';
proc datasets library=work nolist;
save macro all ff3_factors bw all1 beta table1 avgspread alltable1;
run;

proc means data=avgspread noprint;
where &avgexclude;
var excessret;
by portfolio date;
output out=spread(drop=_type_ _Freq_) mean=excessret;
run;

proc sort data=spread;
by date;
run;

data spread;
merge spread(in=in1) ff3_factors(keep=date mktrf smb hml in=in2) bw(in=in3);
by date;
if in1 and in2 and in3;
run;
* conditional return spread ;
proc sort data=spread nodupkey;
by portfolio high date;
where not missing(excessret);
run;

ods output parameterestimates=excessret;
ods listing close;
proc model data=spread;
 by portfolio high;
 endo excessret;
 instruments / intonly;
 parms b0;
 excessret=b0;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=excessret(keep=portfolio high parameter estimate tvalue) out=excessret(keep=portfolio high estimate tvalue);
by portfolio;
where parameter='b0';
run;

ods output parameterestimates=capmalpha;
ods listing close;
proc model data=spread;
 by portfolio high;
 endo excessret;
 exog mktrf;
 instruments _exog_;
 parms b0 b1;
 excessret=b0+b1*mktrf;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=capmalpha(keep=portfolio high parameter estimate tvalue) out=capmalpha(keep=portfolio high estimate tvalue);
by portfolio;
where parameter='b0';
run;

data spread;
set spread;
mktrf1=mktrf*high;
run;

proc sort data=spread nodupkey;
by portfolio date;
where not missing(excessret);
run;

ods output parameterestimates=excessret1;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog high;
 instruments _exog_;
 parms b0 b2;
 excessret=b0+b2*high;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=excessret1(keep=portfolio parameter estimate tvalue) out=excessret1(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b2';
run;

ods output parameterestimates=capmalpha1;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog mktrf high mktrf1;
 instruments _exog_;
 parms b0 b1 b2 b3;
 excessret=b0+b1*mktrf+b2*high+b3*mktrf1;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=capmalpha1(keep=portfolio parameter estimate tvalue) out=capmalpha1(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b2';
run;

data f(drop=high);
merge excessret(where=(high=1) rename=(estimate=exret_high tvalue=tret_high)) capmalpha(where=(high=1) rename=(estimate=alpha_high tvalue=talpha_high)) 
 excessret(where=(high=0) rename=(estimate=exret_low tvalue=tret_low)) capmalpha(where=(high=0) rename=(estimate=alpha_low tvalue=talpha_low)) 
 excessret1(rename=(estimate=exret_diff tvalue=tret_diff)) capmalpha1(rename=(estimate=alpha_diff tvalue=talpha_diff));
by portfolio;
exret_high=exret_high*100;
alpha_high=alpha_high*100;
exret_low=exret_low*100;
alpha_low=alpha_low*100;
exret_diff=exret_diff*100;
alpha_diff=alpha_diff*100;
run;
*************************************;
* predictive reg ;
proc sort data=spread nodupkey;
by portfolio date;
where not missing(excessret);
run;

ods output parameterestimates=excessretbeta;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog bw;
 instruments _exog_;
 parms b0 b1;
 excessret=b0+b1*bw;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=excessretbeta(keep=portfolio parameter estimate tvalue) out=excessretbeta(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b1';
run;

ods output parameterestimates=ff3beta;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog bw mktrf smb hml;
 instruments _exog_;
 parms b0 b1 b2 b3 b4;
 excessret=b0+b1*bw+b2*mktrf+b3*smb+b4*hml;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=ff3beta(keep=portfolio parameter estimate tvalue) out=ff3beta(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b1';
run;

data f1;
merge excessretbeta(rename=(estimate=exret_beta tvalue=tret_beta)) ff3beta(rename=(estimate=ff3_beta tvalue=tff3_beta));
by portfolio;
exret_beta=exret_beta*100;
ff3_beta=ff3_beta*100;
run;

data f;
merge f f1;
by portfolio;
run;

%mend suball;

%macro all(mbeta=,dbeta=,macro=);

proc datasets library=work nolist;
save macro all ff3_factors alltable1;
run;

data mbeta;
set &mbeta;
date=intnx('month',date,1,'e');
format date yymmddn8.;
drop beta1_umd beta1_pead beta1_perf freq_umd freq_pead freq_perf;
run;

proc sort data=mbeta nodupkey;
by permno date;
run;

data dbeta;
set &dbeta;
date=intnx('month',date,1,'e');
format date yymmddn8.;
run;

proc sort data=dbeta nodupkey;
by permno date;
run;

data beta;
merge mbeta(in=in1) dbeta(in=in2);
by permno date;
run;

data all1;
merge all(in=in1) beta(in=in3);
by permno date;
if in1 and in3;
run;

data bw;
set macro(keep=date &macro);
where not missing(&macro);
rename &macro=bw;
run;

proc rank data=bw out=bw group=2 ties=dense;
var bw;
ranks high;
run;

proc sort data=bw;
by date;
run;

data table1;
format macro $24.;
format factor $24.;
format portfolio 8.0;
format exret_high 12.2;
format tret_high 8.2;
format alpha_high 12.2;
format talpha_high 8.2;
format exret_low 12.2;
format tret_low 8.2;
format alpha_low 12.2;
format talpha_low 8.2;
format exret_diff 12.2;
format tret_diff 8.2;
format alpha_diff 12.2;
format talpha_diff 8.2;
format exret_beta 12.2;
format tret_beta 8.2;
format ff3_beta 12.2;
format tff3_beta 8.2;
format firstdate yymmddn8.;
format lastdate yymmddn8.;
run;

data table1;
set table1;
macro="&macro";
run;

data avgspread;
format proxy $24.;
format portfolio 8.0;
format excessret 12.6;
format date yymmddn8.;
run;

%betaport(factor=smb_ff5);
%betaport(factor=hml);
%betaport(factor=rmw);
%betaport(factor=cma);
%betaport(factor=ia);
%betaport(factor=roe);
%betaport(factor=pmu);
%betaport(factor=qmj);
%betaport(factor=umd);
%betaport(factor=mgmt);
%betaport(factor=perf);
%betaport(factor=fin);
%betaport(factor=pead);

* excess return for average portfolio  ;
proc datasets library=work nolist;
save macro all ff3_factors bw all1 beta table1 avgspread alltable1;
run;

proc sort data=avgspread;
by portfolio date;
where not missing(portfolio);
run;

proc means data=avgspread noprint;
var excessret;
by portfolio date;
output out=spread(drop=_type_ _Freq_) mean=excessret;
run;

proc sort data=spread;
by date;
run;

data spread;
merge spread(in=in1) ff3_factors(keep=date mktrf smb hml in=in2) bw(in=in3);
by date;
if in1 and in2 and in3;
run;
* conditional return spread ;
proc sort data=spread nodupkey;
by portfolio high date;
where not missing(excessret);
run;

ods output parameterestimates=excessret;
ods listing close;
proc model data=spread;
 by portfolio high;
 endo excessret;
 instruments / intonly;
 parms b0;
 excessret=b0;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=excessret(keep=portfolio high parameter estimate tvalue) out=excessret(keep=portfolio high estimate tvalue);
by portfolio;
where parameter='b0';
run;

ods output parameterestimates=capmalpha;
ods listing close;
proc model data=spread;
 by portfolio high;
 endo excessret;
 exog mktrf;
 instruments _exog_;
 parms b0 b1;
 excessret=b0+b1*mktrf;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=capmalpha(keep=portfolio high parameter estimate tvalue) out=capmalpha(keep=portfolio high estimate tvalue);
by portfolio;
where parameter='b0';
run;

data spread;
set spread;
mktrf1=mktrf*high;
run;

proc sort data=spread nodupkey;
by portfolio date;
where not missing(excessret);
run;

ods output parameterestimates=excessret1;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog high;
 instruments _exog_;
 parms b0 b2;
 excessret=b0+b2*high;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=excessret1(keep=portfolio parameter estimate tvalue) out=excessret1(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b2';
run;

ods output parameterestimates=capmalpha1;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog mktrf high mktrf1;
 instruments _exog_;
 parms b0 b1 b2 b3;
 excessret=b0+b1*mktrf+b2*high+b3*mktrf1;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=capmalpha1(keep=portfolio parameter estimate tvalue) out=capmalpha1(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b2';
run;

data f(drop=high);
merge excessret(where=(high=1) rename=(estimate=exret_high tvalue=tret_high)) capmalpha(where=(high=1) rename=(estimate=alpha_high tvalue=talpha_high)) 
 excessret(where=(high=0) rename=(estimate=exret_low tvalue=tret_low)) capmalpha(where=(high=0) rename=(estimate=alpha_low tvalue=talpha_low)) 
 excessret1(rename=(estimate=exret_diff tvalue=tret_diff)) capmalpha1(rename=(estimate=alpha_diff tvalue=talpha_diff));
by portfolio;
exret_high=exret_high*100;
alpha_high=alpha_high*100;
exret_low=exret_low*100;
alpha_low=alpha_low*100;
exret_diff=exret_diff*100;
alpha_diff=alpha_diff*100;
run;
*************************************;
* predictive reg ;
proc sort data=spread nodupkey;
by portfolio date;
where not missing(excessret);
run;

ods output parameterestimates=excessretbeta;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog bw;
 instruments _exog_;
 parms b0 b1;
 excessret=b0+b1*bw;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=excessretbeta(keep=portfolio parameter estimate tvalue) out=excessretbeta(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b1';
run;

ods output parameterestimates=ff3beta;
ods listing close;
proc model data=spread;
 by portfolio;
 endo excessret;
 exog bw mktrf smb hml;
 instruments _exog_;
 parms b0 b1 b2 b3 b4;
 excessret=b0+b1*bw+b2*mktrf+b3*smb+b4*hml;
 fit excessret / gmm kernel=(bart,%eval(&lags+1),0) vardef=n; 
run;
quit;
ods listing;

proc sort data=ff3beta(keep=portfolio parameter estimate tvalue) out=ff3beta(keep=portfolio estimate tvalue);
by portfolio;
where parameter='b1';
run;

data f1;
merge excessretbeta(rename=(estimate=exret_beta tvalue=tret_beta)) ff3beta(rename=(estimate=ff3_beta tvalue=tff3_beta));
by portfolio;
factor="avg";
exret_beta=exret_beta*100;
ff3_beta=ff3_beta*100;
run;

data f;
merge f f1;
by portfolio;
run;

proc append data=f base=table1 force;run;
********************************************************;

*  no smb;
%suball(avgexclude=(not(proxy in ("smb","smb_ff5"))));
data f;
set f;
factor="avg_nosmb";
run;

proc append data=f base=table1 force;run;

* no behavior;
%suball(avgexclude=(not(proxy in ("mgmt","perf","fin","pead"))));
data f;
set f;
factor="avg_nobf";
run;

proc append data=f base=table1 force;run;

* no behavior & qmj;
%suball(avgexclude=(not(proxy in ("mgmt","perf","fin","pead","qmj"))));
data f;
set f;
factor="avg_nobfq";
run;

proc append data=f base=table1 force;run;

* no smb & behavior;
%suball(avgexclude=(not(proxy in ("smb","smb_ff5","mgmt","perf","fin","pead"))));
data f;
set f;
factor="avg_nosmbbf";
run;

proc append data=f base=table1 force;run;

* no smb & behavior & qmj;
%suball(avgexclude=(not(proxy in ("smb","smb_ff5","mgmt","perf","fin","pead","qmj"))));
data f;
set f;
factor="avg_nosmbbfq";
run;

proc append data=f base=table1 force;run;

* Organize table based on reported format;
* return ;
proc sort data=table1(keep=factor portfolio exret_high tret_high exret_low tret_low exret_diff tret_diff) 
out=table1_1;
by factor;
where not missing(portfolio);
run;

data table1_t7a(drop=portfolio);
merge table1_1(where=(portfolio=0) rename=(exret_high=exret0_high tret_high=tret0_high exret_low=exret0_low tret_low=tret0_low exret_diff=exret0_diff tret_diff=tret0_diff))
table1_1(where=(portfolio=9) rename=(exret_high=exret9_high tret_high=tret9_high exret_low=exret9_low tret_low=tret9_low exret_diff=exret9_diff tret_diff=tret9_diff))
table1_1(where=(portfolio=66) rename=(exret_high=exret66_high tret_high=tret66_high exret_low=exret66_low tret_low=tret66_low exret_diff=exret66_diff tret_diff=tret66_diff));
by factor;
run;

data table1_t7a;
retain factor exret0_low tret0_low exret0_high tret0_high exret0_diff tret0_diff
exret9_low tret9_low exret9_high tret9_high exret9_diff tret9_diff
exret66_low tret66_low exret66_high tret66_high exret66_diff tret66_diff;
set table1_t7a;
run;

%reorder(indata=table1_t7a);
* CAPM alpha;
proc sort data=table1(keep=factor portfolio alpha_high talpha_high alpha_low talpha_low alpha_diff talpha_diff) 
out=table1_2;
by factor;
where not missing(portfolio);
run;

data table1_t7b(drop=portfolio);
merge table1_2(where=(portfolio=0) rename=(alpha_high=alpha0_high talpha_high=talpha0_high alpha_low=alpha0_low talpha_low=talpha0_low alpha_diff=alpha0_diff talpha_diff=talpha0_diff))
table1_2(where=(portfolio=9) rename=(alpha_high=alpha9_high talpha_high=talpha9_high alpha_low=alpha9_low talpha_low=talpha9_low alpha_diff=alpha9_diff talpha_diff=talpha9_diff))
table1_2(where=(portfolio=66) rename=(alpha_high=alpha66_high talpha_high=talpha66_high alpha_low=alpha66_low talpha_low=talpha66_low alpha_diff=alpha66_diff talpha_diff=talpha66_diff));
by factor;
run;

data table1_t7b;
retain factor alpha0_low talpha0_low alpha0_high talpha0_high alpha0_diff talpha0_diff
alpha9_low talpha9_low alpha9_high talpha9_high alpha9_diff talpha9_diff
alpha66_low talpha66_low alpha66_high talpha66_high alpha66_diff talpha66_diff;
set table1_t7b;
run;

%reorder(indata=table1_t7b);

%mend all;

%let lags=5;

%let filter=( not missing(date) and lagme>=mintna15);
* Table 7 & IA10 & IA16 (PanelC);
*dm log 'clear';
%let minmonth=18;
%let minday=45;

data alltable1;
format macro $24.;
format factor $24.;
format portfolio 8.0;
format exret_high 12.2;
format tret_high 8.2;
format alpha_high 12.2;
format talpha_high 8.2;
format exret_low 12.2;
format tret_low 8.2;
format alpha_low 12.2;
format talpha_low 8.2;
format exret_diff 12.2;
format tret_diff 8.2;
format alpha_diff 12.2;
format talpha_diff 8.2;
format exret_beta 12.2;
format tret_beta 8.2;
format ff3_beta 12.2;
format tff3_beta 8.2;
format firstdate yymmddn8.;
format lastdate yymmddn8.;
run;
*	Table 7 & TIA16 (Panel C), return;
%all(mbeta=mylib.beta1_mf_24,dbeta=mylib.beta2_mf_3,macro=bw);
proc export data=table1_t7a outfile="&mainfolder.\Result\Table7.csv" replace;run;
*	Table IA10, CAPM alpha, the Avg and Avg_nosmb portfolios from this result is the last four rows of Table 7;
proc export data=table1_t7b outfile="&mainfolder.\Result\TableIA10.csv" replace;run;
**************************************************************************************************************;
